<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>no_pk_innodb_tables: common_schema documentation</title>
	<meta name="description" content="no_pk_innodb_tables: common_schema" />
	<meta name="keywords" content="no_pk_innodb_tables: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="no_pk_innodb_tables.html">no_pk_innodb_tables</a></h2>	
<h3>NAME</h3>
no_pk_innodb_tables: List InnoDB tables where no PRIMARY KEY is defined
<h3>TYPE</h3>
View

<h3>DESCRIPTION</h3>

<p>InnoDB uses a clustered B+ tree as underlying data structure. Data is clustered via clustering index, which is the PRIMARY KEY in InnoDB.
It follows that any InnoDB table has a PRIMARY KEY, whether one was explicitly defined or not.
</p>

<p>
When no PRIMARY KEY is defined, InnoDB chooses an existing UNIQUE KEY on the table (but does not let us know which). 
When no such key is available, it creates an internal PRIMARY KEY, based on <i>row id</i>. 
However, it does not provide access to this data.
This leads to a table clustered by some value we cannot access, control, nor define.
It is generally bad practice to create an InnoDB table with no explicit PRIMARY KEY.
</p>

<p><i>no_pk_innodb_tables</i> lists InnoDB tables where PRIMARY KEY is not explicitly created.
It offers a list of candidate keys: UNIQUE keys already defined, which are eligible to take the part of PRIMARY KEY.
</p>

<h3>STRUCTURE</h3>

<blockquote><pre>
mysql&gt; DESC common_schema.no_pk_innodb_tables;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| TABLE_SCHEMA   | varchar(64) | NO   |     |         |       |
| TABLE_NAME     | varchar(64) | NO   |     |         |       |
| ENGINE         | varchar(64) | YES  |     | NULL    |       |
| candidate_keys | longtext    | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
</pre></blockquote>

<h3>SYNOPSIS</h3>

<p>Columns of this view:</p>
<ul>
	<li><strong>TABLE_SCHEMA</strong>: schema of InnoDB table missing PRIMARY KEY</li>
	<li><strong>TABLE_NAME</strong>: InnoDB table missing PRIMARY KEY</li>
	<li><strong>ENGINE</strong>: currently the constant <strong>'InnoDB'</strong></li>
	<li><strong>candidate_keys</strong>: Comma seperated list of candidate (UNIQUE) keys, or NULL if no such keys are available.</li>
</ul>

<h3>EXAMPLES</h3>
<p>Show foreign keys create/drop statements for `sakila`.`film_actor` (depends on `film` and `actor` tables)</p>
<blockquote><pre>mysql&gt; ALTER TABLE `sakila`.`rental` MODIFY rental_id INT NOT NULL, DROP PRIMARY KEY, ADD UNIQUE KEY(rental_id);
mysql&gt; CREATE TABLE `test`.`no_pk` (id INT) ENGINE=InnoDB;

mysql&gt; SELECT * FROM common_schema.no_pk_innodb_tables;
+--------------+------------+--------+-----------------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | candidate_keys        |
+--------------+------------+--------+-----------------------+
| sakila       | rental     | InnoDB | rental_date,rental_id |
| test         | no_pk      | InnoDB | NULL                  |
+--------------+------------+--------+-----------------------+
</pre></blockquote>

<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer

<h3>SEE ALSO</h3>
<a href="candidate_keys.html">candidate_keys</a>,
<a href="redundant_keys.html">redundant_keys</a>
<h3>AUTHOR</h3>
Shlomi Noach
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
